﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace Adrr.DAL
{
    /// <summary>
    /// 数据访问类:Base_Employee
    /// </summary>
    public partial class Base_Employee
    {
        public Base_Employee()
        { }
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(Guid EmployID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Base_Employee");
            strSql.Append(" where EmployID=@EmployID ");
            SqlParameter[] parameters = {
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = EmployID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Adrr.Model.Base_Employee model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Base_Employee(");
            strSql.Append("EmployID,FstName,SecName,Sex,Station,DeptID,Phone,Tel,FamiryTel,Email,Fax,Address,Property,CreateTime,UpdateTime,isDelete,FromServices,HeadUrl)");
            strSql.Append(" values (");
            strSql.Append("@EmployID,@FstName,@SecName,@Sex,@Station,@DeptID,@Phone,@Tel,@FamiryTel,@Email,@Fax,@Address,@Property,@CreateTime,@UpdateTime,@isDelete,@FromServices,@HeadUrl)");
            SqlParameter[] parameters = {
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@FstName", SqlDbType.NVarChar,64),
					new SqlParameter("@SecName", SqlDbType.NVarChar,64),
					new SqlParameter("@Sex", SqlDbType.Bit,1),
					new SqlParameter("@Station", SqlDbType.NVarChar,64),
					new SqlParameter("@DeptID", SqlDbType.VarChar,64),
					new SqlParameter("@Phone", SqlDbType.VarChar,64),
					new SqlParameter("@Tel", SqlDbType.VarChar,64),
					new SqlParameter("@FamiryTel", SqlDbType.VarChar,64),
					new SqlParameter("@Email", SqlDbType.VarChar,64),
					new SqlParameter("@Fax", SqlDbType.VarChar,64),
					new SqlParameter("@Address", SqlDbType.NVarChar,64),
					new SqlParameter("@Property", SqlDbType.VarChar,64),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@UpdateTime", SqlDbType.DateTime),
					new SqlParameter("@isDelete", SqlDbType.Bit,1),
					new SqlParameter("@FromServices", SqlDbType.VarChar,128),
					new SqlParameter("@HeadUrl", SqlDbType.VarChar,250)};
            parameters[0].Value = Guid.NewGuid();
            parameters[1].Value = model.FstName;
            parameters[2].Value = model.SecName;
            parameters[3].Value = model.Sex;
            parameters[4].Value = model.Station;
            parameters[5].Value = model.DeptID;
            parameters[6].Value = model.Phone;
            parameters[7].Value = model.Tel;
            parameters[8].Value = model.FamiryTel;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.Fax;
            parameters[11].Value = model.Address;
            parameters[12].Value = model.Property;
            parameters[13].Value = model.CreateTime;
            parameters[14].Value = model.UpdateTime;
            parameters[15].Value = model.isDelete;
            parameters[16].Value = model.FromServices;
            parameters[17].Value = model.HeadUrl;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Adrr.Model.Base_Employee model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Base_Employee set ");
            strSql.Append("FstName=@FstName,");
            strSql.Append("SecName=@SecName,");
            strSql.Append("Sex=@Sex,");
            strSql.Append("Station=@Station,");
            strSql.Append("DeptID=@DeptID,");
            strSql.Append("Phone=@Phone,");
            strSql.Append("Tel=@Tel,");
            strSql.Append("FamiryTel=@FamiryTel,");
            strSql.Append("Email=@Email,");
            strSql.Append("Fax=@Fax,");
            strSql.Append("Address=@Address,");
            if (!string.IsNullOrEmpty(model.Property))
                strSql.Append("Property=@Property,");
            if (!string.IsNullOrEmpty(model.CreateTime.ToString()))
                strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("UpdateTime=@UpdateTime,");
            if (!string.IsNullOrEmpty(model.isDelete.ToString()))
                strSql.Append("isDelete=@isDelete,");
            strSql.Append("FromServices=@FromServices");//
            if (!string.IsNullOrEmpty(model.HeadUrl))
                strSql.Append(",HeadUrl=@HeadUrl");
            strSql.Append(" where EmployID=@EmployID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FstName", SqlDbType.NVarChar,64),
					new SqlParameter("@SecName", SqlDbType.NVarChar,64),
					new SqlParameter("@Sex", SqlDbType.Bit,1),
					new SqlParameter("@Station", SqlDbType.NVarChar,64),
					new SqlParameter("@DeptID", SqlDbType.VarChar,64),
					new SqlParameter("@Phone", SqlDbType.VarChar,64),
					new SqlParameter("@Tel", SqlDbType.VarChar,64),
					new SqlParameter("@FamiryTel", SqlDbType.VarChar,64),
					new SqlParameter("@Email", SqlDbType.VarChar,64),
					new SqlParameter("@Fax", SqlDbType.VarChar,64),
					new SqlParameter("@Address", SqlDbType.NVarChar,64),
					new SqlParameter("@Property", SqlDbType.VarChar,64),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@UpdateTime", SqlDbType.DateTime),
					new SqlParameter("@isDelete", SqlDbType.Bit,1),
					new SqlParameter("@FromServices", SqlDbType.VarChar,128),
					new SqlParameter("@HeadUrl", SqlDbType.VarChar,250),
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.FstName;
            parameters[1].Value = model.SecName;
            parameters[2].Value = model.Sex;
            parameters[3].Value = model.Station;
            parameters[4].Value = model.DeptID;
            parameters[5].Value = model.Phone;
            parameters[6].Value = model.Tel;
            parameters[7].Value = model.FamiryTel;
            parameters[8].Value = model.Email;
            parameters[9].Value = model.Fax;
            parameters[10].Value = model.Address;
            parameters[11].Value = model.Property;
            parameters[12].Value = model.CreateTime;
            parameters[13].Value = model.UpdateTime;
            parameters[14].Value = model.isDelete;
            parameters[15].Value = model.FromServices;
            parameters[16].Value = model.HeadUrl;
            parameters[17].Value = model.EmployID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(Guid EmployID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Base_Employee ");
            strSql.Append(" where EmployID=@EmployID ");
            SqlParameter[] parameters = {
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = EmployID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 把该列标记为已经删除
        /// </summary>
        public bool SetThisEmployeeIsDeleted(Guid EmployID)
        {
            string strSql = "Update Base_Employee set isDelete='true' where EmployID=@EmployID";
            SqlParameter[] parameters = {
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = EmployID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 把要被删除的这个部门下的所有联系人的部门id设置为要被删除部门的上一级部门id
        /// (如果被删除的组织架构下有联系人，则将联系人移到上一级节点)
        /// </summary>
        public bool UpperEmployeeDept(string delDeptId, string upperDeptId)
        {
            string strSql = "Update Base_Employee set DeptID=@DeptID where DeptID like '"+delDeptId+"%'";
            SqlParameter[] parameters = {
					new SqlParameter("@DeptID", SqlDbType.VarChar,64)			};
            parameters[0].Value =  upperDeptId;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 把该公司下的所有雇员设置为被删除状态
        /// </summary>
        /// <param name="companyId">公司id</param>
        /// <returns></returns>
        public void SetAllThisCompanyEmployeeBeDeleted(string companyId)
        {
            string strSql = "Update Base_Employee set isDelete='true' where DeptID like '" + companyId + "%'";
            DbHelperSQL.ExecuteSql(strSql);
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string EmployIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Base_Employee ");
            strSql.Append(" where EmployID in (" + EmployIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Adrr.Model.Base_Employee GetModel(Guid EmployID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 EmployID,FstName,SecName,Sex,Station,DeptID,Phone,Tel,FamiryTel,Email,Fax,Address,Property,CreateTime,UpdateTime,isDelete,FromServices,HeadUrl from Base_Employee ");
            strSql.Append(" where EmployID=@EmployID ");
            SqlParameter[] parameters = {
					new SqlParameter("@EmployID", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = EmployID;

            Adrr.Model.Base_Employee model = new Adrr.Model.Base_Employee();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Adrr.Model.Base_Employee DataRowToModel(DataRow row)
        {
            Adrr.Model.Base_Employee model = new Adrr.Model.Base_Employee();
            if (row != null)
            {
                if (row["EmployID"] != null && row["EmployID"].ToString() != "")
                {
                    model.EmployID = new Guid(row["EmployID"].ToString());
                }
                if (row["FstName"] != null)
                {
                    model.FstName = row["FstName"].ToString();
                }
                if (row["SecName"] != null)
                {
                    model.SecName = row["SecName"].ToString();
                }
                if (row["Sex"] != null && row["Sex"].ToString() != "")
                {
                    if ((row["Sex"].ToString() == "1") || (row["Sex"].ToString().ToLower() == "true"))
                    {
                        model.Sex = true;
                    }
                    else
                    {
                        model.Sex = false;
                    }
                }
                if (row["Station"] != null)
                {
                    model.Station = row["Station"].ToString();
                }
                if (row["DeptID"] != null)
                {
                    model.DeptID = row["DeptID"].ToString();
                }
                if (row["Phone"] != null)
                {
                    model.Phone = row["Phone"].ToString();
                }
                if (row["Tel"] != null)
                {
                    model.Tel = row["Tel"].ToString();
                }
                if (row["FamiryTel"] != null)
                {
                    model.FamiryTel = row["FamiryTel"].ToString();
                }
                if (row["Email"] != null)
                {
                    model.Email = row["Email"].ToString();
                }
                if (row["Fax"] != null)
                {
                    model.Fax = row["Fax"].ToString();
                }
                if (row["Address"] != null)
                {
                    model.Address = row["Address"].ToString();
                }
                if (row["Property"] != null)
                {
                    model.Property = row["Property"].ToString();
                }
                if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
                }
                if (row["UpdateTime"] != null && row["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(row["UpdateTime"].ToString());
                }
                if (row["isDelete"] != null && row["isDelete"].ToString() != "")
                {
                    if ((row["isDelete"].ToString() == "1") || (row["isDelete"].ToString().ToLower() == "true"))
                    {
                        model.isDelete = true;
                    }
                    else
                    {
                        model.isDelete = false;
                    }
                }
                if (row["FromServices"] != null)
                {
                    model.FromServices = row["FromServices"].ToString();
                }
                if (row["HeadUrl"] != null)
                {
                    model.HeadUrl = row["HeadUrl"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select EmployID,FstName,SecName,Sex,Station,DeptID,Phone,Tel,FamiryTel,Email,Fax,Address,Property,CreateTime,UpdateTime,isDelete,FromServices,HeadUrl ");
            strSql.Append(" FROM Base_Employee ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 联合部门表查询(为了得到该表中部门id所对应的部门名称)
        /// </summary>
        public DataSet GetListJoinDept(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select EmployID,FstName+SecName as Name,Sex,Station,Base_Employee.DeptID,Base_Dept.DeptName,Phone,Tel,FamiryTel,Email,Fax,Address,Property,Base_Employee.CreateTime,Base_Employee.UpdateTime,Base_Employee.isDelete,FromServices,HeadUrl ");
            strSql.Append(" FROM Base_Employee join Base_Dept on Base_Dept.DeptID=Base_Employee.DeptID ");
            strSql.Append(" where Base_Dept.isDelete='false' and Base_Employee.isDelete='false' ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取用到导出Excel的数据
        /// </summary>
        public DataSet GetDataForOutPut(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FstName+SecName as Name,Sex,Station,Base_Dept.DeptName,Phone,Tel,FamiryTel,Email,Fax,Address ");
            strSql.Append(" FROM Base_Employee join Base_Dept on Base_Dept.DeptID=Base_Employee.DeptID ");
            strSql.Append(" where Base_Dept.isDelete='false' and Base_Employee.isDelete='false' ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" EmployID,FstName,SecName,Sex,Station,DeptID,Phone,Tel,FamiryTel,Email,Fax,Address,Property,CreateTime,UpdateTime,isDelete,FromServices,HeadUrl ");
            strSql.Append(" FROM Base_Employee ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Base_Employee ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.EmployID desc");
            }
            strSql.Append(")AS Row, T.*  from Base_Employee T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Base_Employee";
            parameters[1].Value = "EmployID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

